package com.dudsbury.database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.joda.time.DateTime;

public class DatabaseExtension {

	static DBInterface database;

	public static DBInterface getDatabase() {
		return database;
	}

	public static void setDatabase(DBInterface db) {
		database = db;
	}

	// Retreive Items from the database
	public static MyItem[] getItems() {
		database.connect();
		String[][] result = database.returnQuery("select * from Item");
		MyItem items[] = new MyItem[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			items[y] = new MyItem(data);
		}
		database.close();
		return items;
	}

	static int getItemQuantity(String bId, String iId) {
		database.connect();

		iId = iId.replaceAll("\n", "").trim();
		// System.out.printf("BOOKING ID: %s ITEM ID: %s\n",bId,iId);
		String query = String
				.format("select Quantity from Booking_Item where BookingID='%s' and ItemID='%s';",
						bId, iId);
		System.out.println(query);
		String[][] result = database.returnQuery(query);
		database.close();

		int ret;

		try {
			ret = Integer.valueOf(result[0][0]);
		} catch (Exception e) {
			System.out.println("Get item quantity method error");
			ret = 1;
		}

		return ret;

	}

	public static MyItem[] getItemsByBooking(String bId) {
		database.connect();
		bId = bId.trim();
		String[][] result = database
				.returnQuery(String
						.format("select Item.itemID,itemName,ItemDescription,itemType,Price"
								+ " from item, booking_item, booking where booking.bookingid = booking_item.bookingid"
								+ " and booking_item.itemid = item.itemid and booking_item.bookingid = '%s';",
								bId));
		MyItem items[] = new MyItem[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			items[y] = new MyItem(data);
			try {
				items[y].quantity = getItemQuantity(bId, items[y].getItemID());
				items[y].showQuantity = true;
			} catch (Exception e) {
			}
			;
		}
		database.close();
		return items;
	}

	public static void saveItem(MyItem item) {

		if (item.getItemID() != null) {
			item.setItemID(item.getItemID().replaceAll("\n", "").trim());
		}

		// System.out.println("ITEM METHO CONNECTED: " + database.connect());
		boolean alreadyExists = false;

		MyItem items[] = getItems();
		String id = "";
		for (MyItem i : items) {
			i.setItemID(i.getItemID().replaceAll("\n", "").trim());
			if (i.getItemID().equals(item.getItemID())) {
				alreadyExists = true;
				id = i.getItemID();
			}
		}

		id = id.replaceAll("\n", "").trim();

		if (alreadyExists) {
			database.connect();
			// System.out.println(String.format("update item set itemname='%s', itemdescription='%s', itemtype='%s', price='%.2f' where itemId='%s';",
			// item.getItemName(),item.getItemDescription(),
			// item.getItemType(),item.getPrice(),id));
			database.doSQL(String
					.format("update item set itemname='%s', itemdescription='%s', itemtype='%s', price='%.2f' where itemId='%s';",
							item.getItemName(), item.getItemDescription(),
							item.getItemType(), item.getPrice(), id));
			System.out.println("Amending Item");
		} else {
			database.connect();
			database.doSQL(String
					.format("insert into Item (itemId,itemname,itemdescription,itemtype,price) values (null,'%s','%s','%s','%.2f');",
							item.getItemName(), item.getItemDescription(),
							item.getItemType(), item.getPrice()));
			System.out.println("Creating Item");
		}
		database.close();
	}

	// Retreive Rooms from the database
	public static MyRoom[] getRooms() {

		String[][] result = database.returnQuery("select * from Room");
		MyRoom rooms[] = new MyRoom[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			rooms[y] = new MyRoom(data);
		}

		return rooms;
	}

	public static MyRoom[] getRoomsByBooking(String bId) {
		database.connect();
		String bookId = bId.replaceAll("\n", "");

		String[][] result = database
				.returnQuery(String
						.format("select Room.roomId,roomName,priceday,priceNight,roomdescription "
								+ " from booking, room_booking, room where booking.bookingid = room_booking.bookingid and room_booking.roomid = room.roomid "
								+ "and booking.bookingid = '%s';", bookId));

		MyRoom rooms[] = new MyRoom[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			rooms[y] = new MyRoom(data);
		}
		database.close();
		return rooms;
	}

	public static void saveRoom(MyRoom room) {

		boolean alreadyExists = false;

		MyRoom rooms[] = getRooms();
		String id = null;
		for (MyRoom r : rooms) {
			if (r.getRoomName().equals(room.getRoomName())) {
				alreadyExists = true;
				id = r.getRoomID();
			}
		}
		try {
			id = id.replaceAll("\n", "");
		} catch (Exception e) {
			//System.out.println("Error");
		}
		if (alreadyExists) {

			database.doSQL(String
					.format("update Room set Roomname='%s', Roomdescription='%s', priceday='%.2f', pricenight='%.2f' where RoomId='%s';",
							room.getRoomName(), room.getRoomDescription(),
							room.getPriceDay(), room.getPriceNight(), id));
			//System.out.println("Amending Room");
		} else {
			database.doSQL(String
					.format("insert into Room (RoomId,Roomname,priceday,pricenight,Roomdescription) values (null,'%s','%.2f','%.2f','%s');",
							room.getRoomName(), room.getPriceDay(),
							room.getPriceNight(), room.getRoomDescription()));
			//System.out.println("Creating Room");
		}
	}

	/*
	 * // Retreive Customers from the database public static MyCustomer[]
	 * getCustomers() { database.connect();
	 * 
	 * String[][] result = database.returnQuery("select * from Customer");
	 * MyCustomer customers[] = new MyCustomer[result[0].length]; String data[]
	 * = new String[result.length]; for (int y = 0; y < result[0].length; y++) {
	 * for (int x = 0; x < result.length; x++) { data[x] = result[x][y]; }
	 * customers[y] = new MyCustomer(data); } database.close(); return
	 * customers; }
	 */
	public static MyCustomer[] getCustomersByBooking(String bId) {
		database.connect();

		String[][] result = database
				.returnQuery(String
						.format("select Customer.CustomerId,title,forename,surname,postcode,address1,address2, "
								+ " town_or_city,county,telephoneNo,email,inforeference from  booking, customer where booking.bookingid = '%s' "
								+ " and booking.customerid  = customer.customerid;",
								bId));
		MyCustomer customers[] = new MyCustomer[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			customers[y] = new MyCustomer(data);
		}
		database.close();
		return customers;
	}

	/*
	 * public static void saveCustomer(MyCustomer customer) {
	 * 
	 * boolean alreadyExists = false;
	 * 
	 * MyCustomer customers[] = getCustomers(); String id = null; for
	 * (MyCustomer c : customers) { if
	 * (c.getTelephoneNo().equals(customer.getTelephoneNo())) { alreadyExists =
	 * true; id = c.getCustomerID(); } } try { id = id.replaceAll("\n", ""); }
	 * catch (Exception e) { System.out.println("Error saving customer"); } if
	 * (alreadyExists) {
	 * 
	 * database.doSQL(String .format(
	 * "update Customer set title='%d', Forename='%s', Surname='%s', Postcode='%s',Address1='%s',Address2='%s',Town_or_city='%s',County='%s',"
	 * +
	 * " TelephoneNo='%s',Email='%s',InfoReference='%d' where CustomerID='%s' ;"
	 * , customer.getTitleType(), customer.getForename(), customer.getSurname(),
	 * customer.getPostcode(), customer.getAddress1(), customer.getAddress2(),
	 * customer.getTownOrCity(), customer.getCounty(),
	 * customer.getTelephoneNo(), customer.getEmail(),
	 * customer.getInfoReferenceType(), customer.getCustomerID()));
	 * 
	 * database.doSQL(String .format(
	 * "update Customer set title='%s', Forename='%s', Surname='%s', Postcode='%s',Address1='%s',Address2='%s',Town_or_city='%s',County='%s',"
	 * +
	 * " TelephoneNo='%s',Email='%s',InfoReference='%s' where CustomerID='%s' ;"
	 * , customer.getTitleType(), customer.getForename(), customer.getSurname(),
	 * customer.getPostcode(), customer.getAddress1(), customer.getAddress2(),
	 * customer.getTownOrCity(), customer.getCounty(),
	 * customer.getTelephoneNo(), customer.getEmail(),
	 * customer.getInfoReferenceType(), customer.getCustomerID()
	 * 
	 * )); // System.out.println("Amending Customer"); } else {
	 * 
	 * database.doSQL(String .format(
	 * "insert into Customer values (null,'%s','%d','%s','%s','%s','%s','%s','%s','%s','%s','%d');"
	 * , customer.getCustomerID(), customer.getTitleType(),
	 * customer.getForename(), customer.getSurname(), customer.getPostcode(),
	 * customer.getAddress1(), customer.getAddress2(), customer.getTownOrCity(),
	 * customer.getCounty(), customer.getTelephoneNo(), customer.getEmail(),
	 * customer.getInfoReferenceType()));
	 * 
	 * database.doSQL(String .format(
	 * "insert into Customer values (null,'%d','%s','%s','%s','%s','%s','%s','%s','%s','%s','%d');"
	 * , customer.getCustomerID(), customer.getTitleType(),
	 * customer.getForename(), customer.getSurname(), customer.getPostcode(),
	 * customer.getAddress1(), customer.getAddress2(), customer.getTownOrCity(),
	 * customer.getCounty(), customer.getTelephoneNo(), customer.getEmail(),
	 * customer.getInfoReferenceType()));
	 * 
	 * System.out.println("Creating Customer"); } }
	 */
	public static ArrayList<Booking> getBookings() {
		database.connect();
		ArrayList<Booking> bookingList = new ArrayList<Booking>();

		String[][] result = database.returnQuery("select * from Booking;");

		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}

			bookingList.add(new Booking(data));
		}
		return bookingList;
	}

	public static MyPayment getPaymentByBooking(String bId) {
		database.connect();
		String[][] result = database.returnQuery(String.format(
				"select * from Payment where FKBookingID='%s'", bId));
		database.close();
		return new MyPayment(result[0]);
	}

	public static void printBooking(Booking b) {

		System.out.println("Booking ID: " + b.getBookingId());
		System.out.println("Boking Type: " + b.getBookingType());
		System.out.println("Booking Date: " + b.getDate());
		System.out.println("Booking Day: " + b.isBookingDay());
		System.out.println("Booking Night: " + b.isBookingNight());
		System.out.println("Start Time: " + b.getStartTime());
		System.out.println("Finish Time: " + b.getFinishTime());
		System.out.println("Adult: " + b.getOccupancyAdult());
		System.out.println("Child: " + b.getOccupancyChild());
		System.out.println("Deposit: " + b.getDeposit());
		System.out.println("ItemTotal" + b.getItemTotal());
		System.out.println("VAT: " + b.getVat());
		System.out.println("Total: " + b.getTotal());
		System.out.println("Details: " + b.getDetails());

	}

	public static int saveBooking(Booking booking, boolean isNew) {
		System.out.println("BOOKING ID: " + booking);
		if (!isNew) {
		if (booking.getBookingId() != null)
			booking.setBookingId(booking.getBookingId().replaceAll("\n", "")
					.trim());
		}
		
		
		if (isNew==false) {
		String query = String
				.format("update Booking set BookingType='%s',BookingDate='%s',BookingDay='%d',BookingNight='%d',StartTime='%s',FinishTime='%s',OccupancyAdult='%s',OccupancyChild='%s'"
						+ ",Deposit='%s',Details='%s',customerid='%s' where booking.BookingID='%s';",
						booking.getBookingType(), booking.getDateAsString(),
						booking.getDay(), booking.getNight(),
						booking.getStartTime(), booking.getFinishTime(),
						booking.getOccupancyAdult(),
						booking.getOccupancyChild(), booking.getDeposit(),
						booking.getDetails(), booking.getCustomerid(),
						booking.getBookingId());
		System.out.println(query);
		
			//System.out.println(query);
			database.doSQL(query);
			return Integer.valueOf(booking.getBookingId());
		} else {
			String query2 = String
					.format("insert into Booking  values (null,'%s','%s','%d','%d','%s','%s','%s','%s','%s',null,null,null,'%s','%s');",
							booking.getBookingType(),
							booking.getDateAsString(), booking.getDay(),
							booking.getNight(), booking.getStartTime(),
							booking.getFinishTime(),
							booking.getOccupancyAdult(),
							booking.getOccupancyChild(), booking.getDeposit(),
							booking.getDetails(), booking.getCustomerid());
			System.out.println(query2);
			
			int i = database.doSQLGetKey(query2);
			
			return i;
			
			
		}
	}

	public static void saveRoomBooking(String roomId, Booking booking,boolean isNew) {
		roomId = roomId.replaceAll("\n", "");
		String bookingId = booking.getBookingId().replaceAll("\n", "");
		
		System.out.printf("Room: %s Booking %s\n",roomId,bookingId);
		String q = String.format("delete from room_booking where bookingid='%s';",bookingId);
		
		database.doSQL(q);
		
//if (isNew) {
		String query = String.format(
				"insert into Room_Booking values ('%s','%s',null);", roomId,
				bookingId);
		System.out.println(query);
		database.doSQL(query);

}
	

	public static void saveItemBooking(String bookingId, ArrayList<MyItem> items) {

		bookingId = bookingId.replaceAll("\n", "").trim();
		database.doSQL(String.format(
				"delete from Booking_Item where bookingID='%s';", bookingId));

		for (MyItem i : items) {
			String id = i.getItemID().replaceAll("\n", "").trim();

			database.doSQL(String.format(
					"insert into Booking_Item values ('%s','%s','%s',null);",
					bookingId, id, i.quantity));
		}

	}

	public static boolean checkUserCredentials(String name, String pass) {

		database.connect();

		String query = String
				.format("select * from user where username='%s' and userpassword='%s';",
						name, pass);

		String[][] result = database.returnQuery(query);
		database.close();

		if (result[0].length > 0) {
			// database.printQuery(result);
			return true;
		}
		return false;

	}

	public static MyCustomer[] getCustomers() {

		String[][] result = database.returnQuery("select * from Customer");

		MyCustomer customers[] = new MyCustomer[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			customers[y] = new MyCustomer(data);
		//	System.out.println(customers[y].getCustomerID());
		}

		return customers;
	}

	public static MyUser[] getUsers() {
		database.connect();
		// first pair of brackets for column, second one for rows
		String[][] result = database.returnQuery("select * from User");
		database.printQuery(result);
		// if you put result.lenght you are checking the columns
		// if you put result[].lenght you are checking the rows
		MyUser users[] = new MyUser[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			users[y] = new MyUser(data);
			System.out.println(users[y].getUserID());
		}
		database.close();
		return users;
	}

	public static void saveCustomer(MyCustomer customer) {

		if (customer.getCustomerID() != null) {
			customer.setCustomerID(customer.getCustomerID()
					.replaceAll("\n", "").trim());
		} else {
			System.out.println("NULL");
		}
		database.connect();
		boolean alreadyExists = false;
		System.out.println("This is the customer ID: "
				+ customer.getCustomerID());
		MyCustomer customers[] = getCustomers();
		String id = "";
		for (MyCustomer c : customers) {
			System.out.println("This are the customer id in the database" + c);
			c.setCustomerID(c.getCustomerID().replaceAll("\n", "").trim());
			if ((c.getCustomerID().equals(customer.getCustomerID()))) {
				alreadyExists = true;
				id = c.getCustomerID();
				System.out.println("This is the customer ID: " + id);
			}
		}

		id = id.replaceAll("\n", "").trim();

		if (alreadyExists == true) {

			/**
			 * doSQL(String.format(
			 * "update Customer set title='%s', Forename='%s', Surname='%s', Postcode='%s',Address1='%s',Address2='%s',Town_or_city='%s',County='%s',"
			 * +
			 * " TelephoneNo='%s',Email='%s',InfoReference='%s' where CustomerID='%s' ;"
			 * , customer.getTitleType(),customer.getForename(),customer.
			 * getSurname
			 * (),customer.getPostcode(),customer.getAddress1(),customer
			 * .getAddress2(),
			 * customer.getTownOrCity(),customer.getCounty(),customer
			 * .getTelephoneNo
			 * (),customer.getEmail(),customer.getInfoReferenceType
			 * (),customer.getCustomerID()
			 */

			String update = "update Customer set title='"
					+ customer.getTitleType() + "',Forename='"
					+ customer.getForename() + "',Surname='"
					+ customer.getSurname() + "',Postcode='"
					+ customer.getPostcode() + "',Address1='"
					+ customer.getAddress1() + "',Address2='"
					+ customer.getAddress2() + "',Town_or_city='"
					+ customer.getTownOrCity() + "',County='"
					+ customer.getCounty() + "',TelephoneNo='"
					+ customer.getTelephoneNo() + "',Email='"
					+ customer.getEmail() + "',InfoReference='"
					+ customer.getInfoReferenceType() + "'where CustomerID='"
					+ id + "'";

			System.out.println("This is an update " + update);
			database.doSQL(update);
			System.out.println("Amending Customer");
		} else {

			/**
			 * doSQL(String.format(
			 * "insert into Customer values (null,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');"
			 * ,customer.getTitleType(),customer.getForename(),customer.
			 * getSurname
			 * (),customer.getPostcode(),customer.getAddress1(),customer
			 * .getAddress2(),
			 * customer.getTownOrCity(),customer.getCounty(),customer
			 * .getTelephoneNo
			 * (),customer.getEmail(),customer.getInfoReferenceType()));
			 */

			String insert = "insert into Customer values (null,'"
					+ customer.getTitleType() + "','" + customer.getForename()
					+ "','" + customer.getSurname() + "','"
					+ customer.getPostcode() + "','" + customer.getAddress1()
					+ "','" + customer.getAddress2() + "','"
					+ customer.getTownOrCity() + "','" + customer.getCounty()
					+ "','" + customer.getTelephoneNo() + "','"
					+ customer.getEmail() + "','" + customer.getTitleType()
					+ "')";
			System.out.println("This is an insert" + insert);
			database.doSQL(insert);
			System.out.println("Creating Customer");

		}
	}

	public static ArrayList<String> customer() throws SQLException {
		// Create an array list to be filled with group names
		ArrayList<String> groupNames = new ArrayList<String>();
		String query = "SELECT forename FROM customer";

		Statement stmt = null;
		ResultSet rs = stmt.executeQuery(query);

		while (rs.next()) {
			String groupName = rs.getString("forename");
			// add group names to the array list
			groupNames.add(groupName);

		}

		rs.close();
		return groupNames;
	}

	public static boolean saveUser(MyUser user) {

		database.connect();

		if (user.getUserID() != null) {
			user.setUserID(user.getUserID().replaceAll("\n", "").trim());
		}

		boolean alreadyExists = false;

		MyUser users[] = getUsers();
		String id = "";
		for (MyUser i : users) {
			i.setUserID(i.getUserID().replaceAll("\n", "").trim());
			if (i.getUserName().equals(user.getUserName())) {
				alreadyExists = true;
				id = i.getUserID();
			}
		}

		id = id.replaceAll("\n", "").trim();

		if (alreadyExists == false) {
			database.connect();
			database.doSQL(String
					.format("insert into User (userId,UserName,UserPassword) values (null,'%s','%s');",
							user.getUserName(), user.getUserPassword()));
			System.out.println("Creating User");

			database.close();
		}
		return alreadyExists;
	}

	public static boolean deleteBooking(String bId) {

		bId = bId.replaceAll("\n", "").trim();
		database.connect();

		String query = String.format(
				"delete from booking where bookingid='%s';", bId);
		if (database.doSQL(query)) {
			database.close();
			return true;
		} else {
			database.close();
			return false;
		}

	}
	
	public static boolean checkBookingByRoomDate(Booking b, MyRoom r) {
		
		String date = String.format("%d-%d-%d", b.getDate().getYear(),b.getDate().getMonthOfYear(),b.getDate().getDayOfMonth());
		String rId = r.getRoomID().replaceAll("\n", "").trim();
		
		String query = String.format("select from booking where bookingdate='%s' and booking.booking_id=room_booking.bookingid " +
				"and room_booking.roomid='%s';",date,rId);
		
		String[][] value = database.returnQuery(query);
		System.out.println("Result Length: " + value.length);
		if (value.length<1)
			return true;
		else return false;
	}

}
